﻿CREATE PROCEDURE [dbo].[utl_CreateSPTvf]
@Table [sysname], @ParentObject [sysname]=NULL
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @True  BIT; SET @True  = 1
	DECLARE @False BIT; SET @False = 0
	
	DECLARE @CRLF VARCHAR(2); SET @CRLF = CHAR(13) + CHAR(10)
	DECLARE @TAB VARCHAR(1);  SET @TAB = CHAR(9)
	
	DECLARE @INDENT INT; SET @INDENT = 4
	
	DECLARE @ID                   INT
	DECLARE @SQL                  NVARCHAR(MAX)
	DECLARE @ColumnSelectList     VARCHAR(MAX)
	DECLARE @ColumnUpdateList     VARCHAR(MAX)
	DECLARE @ColumnVariablesList  VARCHAR(MAX)
	DECLARE @WhereClause          VARCHAR(MAX)
	DECLARE @WhereClause2         VARCHAR(MAX)
	DECLARE @FunctionName        sysname
	DECLARE @ColumnName           sysname
	DECLARE @Datatype             sysname
	DECLARE @QuotedColumnName     sysname
	DECLARE @VariableColumnName   sysname
	DECLARE @IsPartOfPrimaryKey   BIT
	DECLARE @IsIdentity           BIT
	DECLARE @IsTimestamp          BIT
	DECLARE @IsComputed           BIT
	DECLARE @NumericPrecision     SMALLINT
	DECLARE @NumericScale         SMALLINT
	DECLARE @MaxLength            INT
	
	DECLARE @Out TABLE (
				ID INTEGER IDENTITY(1, 1) PRIMARY KEY, 
				Line VARCHAR(MAX)
			)
	
	DECLARE @Columns  TABLE (
	            ID INTEGER IDENTITY(1, 1) PRIMARY KEY,
	            ColumnName sysname,
	            QuotedColumnName sysname,
	            VariableColumnName sysname,
	            Datatype sysname,
	            MaxLength INT,
	            IsIdentity BIT,
	            IsTimestamp BIT,
	            IsComputed BIT,
	            IsPartOfPrimaryKey BIT,
	            IsNullable BIT,
	            NumericPrecision SMALLINT,
	            NumericScale SMALLINT
	        )
	
	-- make sure table exists    
	IF dbo.fn_TableExists(@Table) = @False
	BEGIN
	    SET @SQL = 'Table ''%s'' does not exist.'
	    RAISERROR(@SQL, 16, 1, @Table)
	    RETURN 1
	END
	
	-- make sure table has a primary key
	IF dbo.fn_TableHasPrimaryKey(@Table) = @False
	BEGIN
	    SET @SQL = 'Table ''%s'' does not have a primary key.'
	    RAISERROR(@SQL, 16, 1, @Table)
	    RETURN 2
	END
	
	-- make sure table has a timestamp column
	IF dbo.fn_TableHasTimestamp(@Table) = @False
	BEGIN
	    SET @SQL = 'Table ''%s'' does not have a timestamp column.'
	    RAISERROR(@SQL, 16, 1, @Table)
	    RETURN 3
	END
	
	IF @ParentObject IS NULL
	BEGIN
	    EXECUTE [dbo].[utl_CreateSPScriptTable]
	    SET @ParentObject = OBJECT_NAME(@@PROCID)
	END
	
	-- build name of function to create
	SET @FunctionName = 'tvf_' + @Table -- + '_Sel'
	
	SET @SQL = 'DECLARE @True  BIT; SET @True  = 1'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'DECLARE @False BIT; SET @False = 0'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = ''    
	INSERT INTO @Out (Line) VALUES (@SQL)
	  
	-- force any existing function to be deleted
	SET @SQL = 'IF dbo.fn_FunctionExists(''' + @FunctionName + ''') = @True'
	INSERT INTO @Out (Line) VALUES (@SQL)
    
	SET @SQL = SPACE(@INDENT) + 'DROP FUNCTION dbo.' + dbo.fn_QuoteName(@FunctionName)
	INSERT INTO @Out (Line) VALUES (@SQL)
        
    SET @SQL = 'GO'    
	INSERT INTO @Out (Line) VALUES (@SQL)
    
    SET @SQL = ''    
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	-- create a table of column names and certain attributes
	INSERT INTO @Columns
	  (
	    ColumnName,
	    QuotedColumnName,
	    VariableColumnName,
	    Datatype,
	    MaxLength,
	    IsIdentity,
	    IsTimestamp,
	    IsComputed,
	    IsPartOfPrimaryKey,
	    IsNullable,
	    NumericPrecision,
	    NumericScale
	  )
	SELECT ColumnName,
	       QuotedColumnName,
	       VariableColumnName,
	       Datatype,
	       MaxLength,
	       IsIdentity,
	       IsTimestamp,
	       IsComputed,
	       IsPartOfPrimaryKey,
	       IsNullable,
	       NumericPrecision,
	       NumericScale
	FROM   dbo.fn_ColumnsAndAttributes(@Table)
	
	-- build proc name
	SET @SQL = 'CREATE FUNCTION dbo.' + dbo.fn_QuoteName(@FunctionName) + ' ('
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	-- build parameter list based on primary key columns
	SELECT @ID = MIN(ID)
	FROM   @Columns
	WHERE  IsPartOfPrimaryKey = @True
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SELECT @ColumnName = ColumnName,
	           @QuotedColumnName = QuotedColumnName,
	           @VariableColumnName = VariableColumnName,
	           @Datatype = Datatype,
	           @MaxLength = MaxLength,
	           @IsIdentity = IsIdentity,
	           @IsTimestamp = IsTimestamp,
	           @IsComputed = IsComputed,
	           @NumericPrecision = NumericPrecision,
	           @NumericScale = NumericScale
	    FROM   @Columns
	    WHERE  ID = @ID
	    
	    SET @SQL = SPACE(@INDENT) + @VariableColumnName
	    SET @SQL = @SQL + ' ' + UPPER(@Datatype)
	    IF @Datatype IN ('VARCHAR', 'CHAR', 'NVARCHAR', 'NCHAR')
	        SET @SQL = @SQL + '(' + CAST(@MaxLength AS VARCHAR(4)) + ')'
	    
	    IF @Datatype IN ('NUMERIC', 'DECIMAL')
	        SET @SQL = @SQL + '(' + CAST(@NumericPrecision AS VARCHAR(2)) + ',' 
	            + CAST(@NumericScale AS VARCHAR(2)) + ')'
	    
	    SELECT @ID = MIN(ID)
	    FROM   @Columns
	    WHERE  ID > @ID
	           AND IsPartOfPrimaryKey = @True
	    
	    IF @ID IS NULL
	        SET @SQL = @SQL + ')'
	    ELSE
	        SET @SQL = @SQL + ','
	    
		INSERT INTO @Out (Line) VALUES (@SQL)
	END
	
	-- build list for select and update
	SELECT @ID = MIN(ID)
	FROM   @Columns
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SELECT @ColumnName = ColumnName,
	           @QuotedColumnName = QuotedColumnName,
	           @VariableColumnName = VariableColumnName,
	           @Datatype = Datatype,
	           @MaxLength = MaxLength,
	           @IsIdentity = IsIdentity,
	           @IsTimestamp = IsTimestamp,
	           @IsComputed = IsComputed
	    FROM   @Columns
	    WHERE  ID = @ID
	    -- build list of columns for select
	    SET @ColumnSelectList = ISNULL(@ColumnSelectList + ', ', '') + @QuotedColumnName
	    -- filter out columns that we should not update
	    IF ((@IsIdentity = @False) AND (@IsTimestamp = @False) AND (@IsComputed = @False))
	        -- list of variables to use for updating columns
	        SET @ColumnVariablesList = ISNULL(@ColumnVariablesList + ', ', '') + 
	            @VariableColumnName
	    
	    SELECT @ID = MIN(ID)
	    FROM   @Columns
	    WHERE  ID > @ID
	END
	
	-- tack on housekeeping columns for the refresh select
	SET @ColumnSelectList = @ColumnSelectList + dbo.fn_HousekeepingColumnList()
	
	-- build where clause based on primary key columns
	SELECT @ID = MIN(ID)
	FROM   @Columns
	WHERE  IsPartOfPrimaryKey = @True
	
	WHILE @ID IS NOT NULL
	BEGIN
	    SELECT @ColumnName = ColumnName,
	           @QuotedColumnName = QuotedColumnName,
	           @VariableColumnName = VariableColumnName,
	           @Datatype = Datatype,
	           @MaxLength = MaxLength,
	           @IsIdentity = IsIdentity,
	           @IsTimestamp = IsTimestamp,
	           @IsComputed = IsComputed
	    FROM   @Columns
	    WHERE  ID = @ID
	    
	    SET @WhereClause = ISNULL(@WhereClause + ' AND ', '') + '(' + @QuotedColumnName 
	        + ' = ' + @VariableColumnName + ')'
	    
	    SELECT @ID = MIN(ID)
	    FROM   @Columns
	    WHERE  ID > @ID
	           AND IsPartOfPrimaryKey = @True
	END
	
	SET @SQL = 'RETURNS TABLE'
	INSERT INTO @Out (Line) VALUES (@SQL)

	SET @SQL = 'AS'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'RETURN'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = '('
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = SPACE(@INDENT) + 'SELECT ' + @ColumnSelectList
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = SPACE(@INDENT) + 'FROM ' + dbo.fn_QuoteName(@Table)
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = SPACE(@INDENT) + 'WHERE ' + @WhereClause
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	--SET @SQL = SPACE(@INDENT) + 'OPTION (FAST 1)'
	--INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ')'
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = 'GO'    
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = ''
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	SET @SQL = REPLICATE('-', 80)
	INSERT INTO @Out (Line) VALUES (@SQL)
	
	INSERT INTO CreateSPScriptTable
	  (
	    Line
	  )
	SELECT Line
	FROM   @Out
	ORDER BY
	       ID
	
	IF @ParentObject = OBJECT_NAME(@@PROCID)
	BEGIN
	    SELECT Line
	    FROM   CreateSPScriptTable
	    ORDER BY
	           ID
	END
	
	SET NOCOUNT OFF
	
	RETURN 0
END

